﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using C1.Win.C1FlexGrid;
using System.Transactions;
using System.IO;
using BHXH_NT.XML;
namespace BHXH_NT
{
    public partial class frmSosotam : Form
    {
        DataTable tb;
        private const string XML_FILE_XA = "XaObject.xml";
        private const string XML_FILE_KHOI = "KhoiObject.xml";
        public frmSosotam()
        {
            InitializeComponent();
        }
        private void cmdImport_Click(object sender, EventArgs e)
        {
            OpenFileDialog oFile = new OpenFileDialog();
            oFile.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*";
            if (oFile.ShowDialog() == DialogResult.OK)
            {
                txtDuongdan.Text = oFile.FileName;
            }
        }
        private void frmSosotam_Load(object sender, EventArgs e)
        {
            txtDuongdan.Text = System.IO.Directory.GetCurrentDirectory().ToString() + "\\thu_bhyt.xls";
            txtMaqt.Text = ModuleBHXH.strMonths;
            cboLoaiHSCN.SelectedIndex = 0;
            cboloai.SelectedIndex = 0;
            LoadDataXa();
            LoadDataKhoi();
            LoadMadvitoForm();

        }
        private void btnSolieu_Click(object sender, EventArgs e)
        {

            tb = importDataFromExcel().Tables[0];
            fa.DataSource = tb;
            FormatGrid();
        }
        public DataSet importDataFromExcel()
        {
            //string FileName = System.IO.Directory.GetCurrentDirectory().ToString() + "\\thu_bhxh.xls";
            string FileName = txtDuongdan.Text;
            OleDbDataAdapter myAdapter = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            // Connection String to Excel Workbook,Replace DataSource value to point to your excel file location
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + FileName + " ;Extended Properties=Excel 8.0";

            // Create Connection to Excel Workbook
            using (OleDbConnection connection =
                         new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand
                        ("Select *,'" + cboMadvi.Text.Substring(0,7) + "' as MADVI FROM [Sheet1$]", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                command.ExecuteNonQuery();
                myAdapter.SelectCommand = command;
                myAdapter.Fill(ds);
                

            }
            return ds;
        }      
        private void FormatGrid()
        {
            fa.Cols[1].WidthDisplay = 50;
            fa.Cols[2].WidthDisplay = 90;
            fa.Cols[2].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[3].WidthDisplay = 130;
            fa.Cols[3].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[4].WidthDisplay = 160;
            fa.Cols[4].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[5].WidthDisplay = 100;
            fa.Cols[5].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[6].WidthDisplay = 45;
            fa.Cols[6].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[7].WidthDisplay = 50;
            fa.Cols[7].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[8].WidthDisplay = 235;
            fa.Cols[8].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[9].WidthDisplay = 235;
            fa.Cols[9].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[10].WidthDisplay = 235;
            fa.Cols[14].WidthDisplay = 235;
            fa.Cols[14].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[15].WidthDisplay = 235;
            fa.Cols[15].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[16].WidthDisplay = 235;
            fa.Cols[16].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[17].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[18].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[19].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[20].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[21].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[22].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[23].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[24].StyleDisplay.Font = new Font(".Vntime", 10);
         
        }
        private void FormatGrid1()
        {   
            fa.Cols[1].WidthDisplay = 90;
            fa.Cols[1].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[2].WidthDisplay = 100;
            fa.Cols[2].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[3].WidthDisplay = 140;
            fa.Cols[3].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[4].WidthDisplay = 70;
            fa.Cols[4].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[5].WidthDisplay = 120;
            fa.Cols[5].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[6].WidthDisplay = 80;
            fa.Cols[6].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[6].Format = "dd/MM/yyyy";
            fa.Cols[7].WidthDisplay = 20;
            fa.Cols[7].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[8].WidthDisplay = 235;
            fa.Cols[8].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[9].WidthDisplay = 235;
            fa.Cols[9].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[10].WidthDisplay = 100;
            fa.Cols[10].StyleDisplay.Font = new Font(".Vntime", 10);
           
           

        }
        private void canhbaotrung()
        {
            if (fa.Rows.Count <= 1) { return; }
            //Int64 stt = Convert.ToInt64(fa.Rows[1]["madvi"]);
            //fa.Rows[1][0] = 1;
            for (int i = 1; i <= fa.Rows.Count - 1; i++)
            {
                if (fa.Rows[i]["sokcb"].ToString().Trim() != "")
                {
                    C1.Win.C1FlexGrid.CellStyle rs = this.fa.Styles.Add("rowcolor");
                    rs.ForeColor = Color.Red;
                    fa.Rows[i].Style = rs;
                }
                //stt = Convert.ToInt64(fa.Rows[i]["stt"]);
                //fa.Rows[i][0] = Convert.ToInt64(fa.Rows[i - 1][0]) + 1;
            }
        }
        private void canhbaotrungmax()
        {
            if (fa.Rows.Count <= 1) { return; }
            string sobhxh = Convert.ToString(fa.Rows[1]["sobhxh"]);
            fa.Rows[1][0] = 1;
            for (int i = 2; i <= fa.Rows.Count - 1; i++)
            {
                if (sobhxh == Convert.ToString(fa.Rows[i]["sobhxh"]))
                {
                    C1.Win.C1FlexGrid.CellStyle rs = this.fa.Styles.Add("rowcolor");
                    rs.ForeColor = Color.Red;
                    fa.Rows[i].Style = rs;
                }
                sobhxh = Convert.ToString(fa.Rows[i]["sobhxh"]);
                fa.Rows[i][0] = Convert.ToInt64(fa.Rows[i-1][0]) + 1;
            }
        }
        private bool CheckCondtion()
        {
            if (cboMadvi.Text.Substring(0,7) == "")
            {
                MessageBox.Show("Nhập mã đơn vị");
                cboMadvi.Focus();
                return false;

            }
            if (cboLoaiHSCN.SelectedIndex == -1)
            {
                MessageBox.Show("Nhập loại hồ sơ cá nhân");
                cboLoaiHSCN.Focus();
                return false;
            }
            if (cboloai.SelectedIndex == -1)
            {
                MessageBox.Show("Nhập loại mã đơn vị hay mã khối");
                cboloai.Focus();
                return false;
            }
            if (cboMadvi.Text.Substring(0,7).Length != 7)
            {
                MessageBox.Show("Nhập lại mã đơn vị");
                cboMadvi.Focus();
                return false;
            }
            if (cboloai.SelectedIndex == 1 && cboMakhoi.Text == "")
            {
                MessageBox.Show("Nhập Mã khối");
                cboMakhoi.Focus();
                return false;
            }
            if (fa.Rows[1][1]== null)
            {
                MessageBox.Show("nhấn nút số liệu");
                btnSolieu.Focus();
                return false;
            }
            return true;
        }
        private void cmdSoso_Click(object sender, EventArgs e)
        {
            if (CheckCondtion() == false) { return; }
            Cursor.Current = Cursors.WaitCursor;
            Importfile(0);
            FormatGrid();
            canhbaotrung();
            Cursor.Current = Cursors.Default;
        }
        private string hosocn()
        {
            string madvi = "";
            if (cboloai.SelectedIndex == 0)
            {
                madvi = "MADVI = '" + cboMadvi.Text.Substring(0,7) + "'";
            }
            else
            {
                madvi = "left(MADVI,2) = '" + cboMakhoi.Text.Substring(0,2) + "'";
            }
            string sql = " select a.stt, b.sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk from ("
            + "  SELECT stt,hoten,ngaysinh,"
            + "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
            + "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
            + "  case when namsinh = 'X' then ngaysinh else right(left(ngaysinh,10),4) end as nam,"
            + "  gioitinh,diachi"
            + "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0,7) + "'"
            + "  ) a left join "
            + "  (SELECT Sobhxh,rtrim(Ho) + ' ' + rtrim(Ten) AS hoten, Sokcb, "
            + " case when y.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), y.ngaysinh, 103),2) end as ngay,"
            + " case when y.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), y.ngaysinh, 103),5),2) end as thang,"
            + " right(left(CONVERT(VARCHAR(10), y.ngaysinh, 103),10),4) as nam,"
            + " y.Gioitinh,"
            + " y.Diachi, y.DiachiHK, y.Mapb,y.madvi "
            + " FROM HOSOCN y where Sobhxh<>'' and " + madvi + " and left(sobhxh,1) = 'Y'"
            + " ) b on a.hoten = b.hoten and a.nam = b.nam"
            + "  and a.gioitinh = b.gioitinh"
            + " order by a.stt ";
            //and a.ngay = b.ngay and a.thang = b.thang
            return sql;
        }
        private string hosocnmax()
        {
            string madvi = "";
            if (cboloai.SelectedIndex == 0)
            {
                madvi = "MADVI = '" + cboMadvi.Text.Substring(0,7) + "'";
            }
            else
            {
                madvi = "left(MADVI,2) = '" + cboMakhoi.Text + "'";
            }
            string sql = " select a.stt, 'Y' + b.sobhxh as sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk from ("
            + "  SELECT stt,hoten,ngaysinh,"
            + "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
            + "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
            + "  case when namsinh = 'X' then ngaysinh else right(left(ngaysinh,10),4) end as nam,"
            + "  gioitinh,diachi"
            + "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0,7) + "'"
            + "  ) a left join "
            + "  (SELECT x.Sobhxh,x.hoten, y.Sokcb, "
            + " case when y.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), y.ngaysinh, 103),2) end as ngay,"
            + " case when y.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), y.ngaysinh, 103),5),2) end as thang,"
            + " right(left(CONVERT(VARCHAR(10), y.ngaysinh, 103),10),4) as nam,"
            + " y.Gioitinh,"
            + " y.Diachi, y.DiachiHK, y.Mapb,y.madvi from ("
            + " SELECT MAX(RIGHT(RTRIM(C.SOBHXH),9)) AS SOBHXH ,"
            + " rtrim(c.Ho) + ' ' + rtrim(c.Ten) AS HOTEN,c.NGAYSINH,c.GIOITINH "
            + " FROM HOSOCN c where Sobhxh<>'' and  " + madvi + "  and left(c.sobhxh,1) = 'Y'"
            + " group by rtrim(c.Ho) + ' ' + rtrim(c.Ten),NGAYSINH,GIOITINH "
            + " ) x INNER JOIN HOSOCN y ON 'Y' + x.SOBHXH = y.SOBHXH "
            + " ) b on a.hoten =b.hoten and a.nam = b.nam"
            + "  and a.gioitinh = b.gioitinh"
            + " order by a.stt ";
            //and a.ngay = b.ngay and a.thang = b.thang 
            return sql;
        }
        private string hosothamgiamax()
        {
            string hoso="";
            string madvi = "";
            if (cboLoaiHSCN.SelectedIndex == 0)
            {
                hoso = "<>";
            }
            else
            {
                hoso = "=";
            }
            if (cboloai.SelectedIndex == 0)
            {
                madvi = "b.MADVI = '" + cboMadvi.Text.Substring(0,7) + "'";
            }
            else
            {
                madvi = "left(b.MADVI,2) = '" + cboMakhoi.Text + "'";
            }
            string sql = " select a.stt, 'Y' + b.sobhxh as sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk from ("
            + "  SELECT stt,hoten,ngaysinh,"
            + "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
            + "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
            + "  case when namsinh = 'X' then ngaysinh else right(left(ngaysinh,10),4) end as nam,"
            + "  gioitinh,diachi"
            + "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0,7) + "'"
            + "  ) a left join "
            + "  (SELECT x.Sobhxh,x.hoten, y.Sokcb, "
            + " case when y.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), y.ngaysinh, 103),2) end as ngay,"
            + " case when y.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), y.ngaysinh, 103),5),2) end as thang,"
            + " right(left(CONVERT(VARCHAR(10), y.ngaysinh, 103),10),4) as nam,"
            + " y.Gioitinh,"
            + " y.Diachi, y.DiachiHK, y.Mapb,y.madvi from ("
            + " SELECT MAX(RIGHT(RTRIM(b.SOBHXH),9)) AS SOBHXH ,"
            + " rtrim(c.Ho) + ' ' + rtrim(c.Ten) AS HOTEN,c.NGAYSINH,c.GIOITINH "
            + " FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            + " (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL b WHERE " + madvi
            + " AND LEFT(Maqt,6)=denthang AND LEFT(Maqt,6)<='" + txtMaqt.Text + "'  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + " GROUP BY Sobhxh, Loaidt, Ma_Nte) a  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            + " AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang AND " + madvi
            + " AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt " + hoso + " 0 AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 and left(b.sobhxh,1) = 'Y'"
            + " group by rtrim(c.Ho) + ' ' + rtrim(c.Ten),NGAYSINH,GIOITINH "
            + " ) x INNER JOIN HOSOCN y ON 'Y' + x.SOBHXH = y.SOBHXH "
            + " ) b on a.hoten = b.hoten  and a.nam = b.nam"
            + "  and a.gioitinh = b.gioitinh"
            + " order by a.stt ";
            //and a.ngay = b.ngay and a.thang = b.thang
            return sql;

        }
        private string hosothamgia()
        {
            string hoso = "";
            string madvi = "";
            if (cboLoaiHSCN.SelectedIndex == 0)
            {
                hoso = "<>";
            }
            else
            {
                hoso = "=";
            }
            if (cboloai.SelectedIndex == 0)
            {
                madvi = "b.MADVI = '" + cboMadvi.Text.Substring(0,7) + "'";
            }
            else
            {
                madvi = "left(b.MADVI,2) = '" + cboMakhoi.Text + "'";
            }
            // chi co nam sinh
            string sql = " select a.stt, b.sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk from ("
            + "  SELECT stt,hoten,ngaysinh,"
            + "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
            + "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
            + "  case when namsinh = 'X' then ngaysinh else right(left(ngaysinh,10),4) end as nam,"
            + "  gioitinh,diachi"
            + "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0, 7) + "'"
            + "  ) a left join "
            + "  (SELECT b.Sobhxh,rtrim(c.Ho) + ' ' + rtrim(c.Ten) as hoten, c.Sokcb, "
            + "  case when c.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), c.ngaysinh, 103),2) end as ngay,"
            + "  case when c.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),5),2) end as thang,"
            + "  right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),10),4) as nam,"
            + "  c.Gioitinh,"
            + "  c.Diachi, c.DiachiHK, c.Mapb,b.madvi"
            + "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            + "  (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL b WHERE " + madvi
            + "  AND LEFT(Maqt,6)=denthang AND LEFT(Maqt,6)<='" + txtMaqt.Text + "'  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + "  GROUP BY Sobhxh, Loaidt, Ma_Nte) a  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            + "  AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang AND " + madvi
            + "  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt " + hoso + " 0 AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 and left(b.sobhxh,1) = 'Y'"
            + "  ) b on a.hoten = b.hoten and a.nam = b.nam"
            + "  and a.gioitinh = b.gioitinh"
            + "  order by a.stt ";
            // co ngay thang nam sinh
            //string sql = " select a.stt, b.sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk from ("
            //+ "  SELECT stt,hoten,ngaysinh,"
            //+ "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
            //+ "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
            //+ "  case when namsinh = 'X' then ngaysinh else right(left(ngaysinh,10),4) end as nam,"
            //+ "  gioitinh,diachi"
            //+ "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0,7) + "'"
            //+ "  ) a left join "
            //+ "  (SELECT b.Sobhxh,rtrim(c.Ho) + ' ' + rtrim(c.Ten) as hoten, c.Sokcb, "
            //+ "  case when c.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), c.ngaysinh, 103),2) end as ngay,"
            //+ "  case when c.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),5),2) end as thang,"
            //+ "  right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),10),4) as nam,"
            //+ "  c.Gioitinh,"
            //+ "  c.Diachi, c.DiachiHK, c.Mapb,b.madvi"
            //+ "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            //+ "  (SELECT Sobhxh, Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL b WHERE " + madvi
            //+ "  AND LEFT(Maqt,6)=denthang AND LEFT(Maqt,6)<='" + txtMaqt.Text + "'  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            //+ "  GROUP BY Sobhxh, Loaidt, Ma_Nte) a  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            //+ "  AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang AND " + madvi
            //+ "  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt " + hoso + " 0 AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 and left(b.sobhxh,1) = 'Y'"
            //+ "  ) b on a.hoten = b.hoten  and a.ngay = b.ngay and a.thang = b.thang and a.nam = b.nam"
            //+ "  and a.gioitinh = b.gioitinh"
            //+ "  order by a.stt ";
           
           return sql;
        }
       
        private string kiemtratrungthe()
        {      
            
            string sql = " select a.stt, b.sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk,a.mapb from ("
            + "  SELECT stt,hoten,ngaysinh,"
            + "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
            + "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
            + "  case when namsinh = 'X' then ngaysinh else right(left(ngaysinh,10),4) end as nam,"
            + "  gioitinh,diachi,mapb"
            + "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0,7) + "'"
            + "  ) a left join "
            + "  (SELECT b.Sobhxh,rtrim(c.Ho) + ' ' + rtrim(c.Ten) as hoten, c.Sokcb, "
            + "  case when c.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), c.ngaysinh, 103),2) end as ngay,"
            + "  case when c.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),5),2) end as thang,"
            + "  right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),10),4) as nam,"
            + "  c.Gioitinh,"
            + "  c.Diachi, c.DiachiHK, c.Mapb,b.madvi"
            + "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            + "  (SELECT Sobhxh, b.Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL b, DMDONVI c WHERE c.Madvi=b.Madvi  AND "
            + "  (c.MA_XA = '" + cboMadvi.Text.Substring(cboMadvi.Text.Length - 2, 2) + "' or c.MA_XA = '99') AND LEFT(Maqt,6)=denthang AND LEFT(Maqt,6)<='" + txtMaqt.Text + "'  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + "  GROUP BY Sobhxh, Loaidt, b.Ma_Nte) a  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            + "  AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang "
            + "  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt <> 0 AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 and left(b.sobhxh,1) = 'Y'"
            + "  ) b on a.hoten = b.hoten and a.ngay = b.ngay and a.thang = b.thang and a.nam = b.nam"
            + "  and a.gioitinh = b.gioitinh "
            + "  order by a.stt ";
            return sql;
        }
        private string kiemtratrungthe(string ho, string ten, string ngaysinh, string gioitinh, string maqt)
        {
            string sql = " SELECT TOP 3 a.Madvi, a.Sobhxh, a.Sokcb, a.Ho, a.Ten, a.Ngaysinh, "
            + "  a.Gioitinh, a.Socmnd, a.Diachi, a.Mapb FROM HOSOCN a  "
            + "  WHERE a.Ho='" + ho + "'  AND a.Ten='" + ten + "'  AND a.Ngaysinh='" + ngaysinh + "'   AND a.Gioitinh = " + gioitinh
            + "  AND EXISTS  (SELECT g.Sobhxh FROM HSC47_DTL g,  "
            + "  (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt FROM HSC47_DTL a, DMDONVI b "
            + "  WHERE a.Madvi=b.Madvi and (b.MA_XA = '" + cboMadvi.Text.Substring(cboMadvi.Text.Length - 2, 2) + "' or b.MA_XA = '99') AND LEFT(a.Maqt,6)=a.Denthang AND "
            + "  LEFT(a.Maqt,6)<='" + maqt + "'  AND CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + "  GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) f  WHERE a.Sobhxh=g.Sobhxh AND g.Sobhxh=f.Sobhxh "
            + "  AND g.Loaidt=f.Loaidt AND g.Ma_Nte=f.Ma_Nte AND g.Maqt=f.Maqt AND LEFT(g.Maqt,6)=g.Denthang "
            + "  AND g.Madvi=f.Madvi  AND g.Hsl+g.Pc1+g.Pc2+g.Pc3+g.Pc4+g.Pc5+g.Ml+g.Mlyt<>0 and left(a.sobhxh,1) = 'Y' AND "
            + "  CHARINDEX(g.Pa,'TT/TU/TN/GN/DN/CD')=0)";
            return sql;
        }
        private string kiemtratrungtheTE()
        {
            string sql = " select a.stt, b.sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk,a.mapb from ("
            + "  SELECT stt,hoten,ngaysinh,"
            + "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
            + "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
            + "  case when namsinh = 'X' then ngaysinh else right(left(ngaysinh,10),4) end as nam,"
            + "  gioitinh,diachi,MAPB"
            + "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0, 7) + "'"
            + "  ) a left join "
            + "  (SELECT b.Sobhxh,rtrim(c.Ho) + ' ' + rtrim(c.Ten) as hoten, c.Sokcb, "
            + "  case when c.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), c.ngaysinh, 103),2) end as ngay,"
            + "  case when c.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),5),2) end as thang,"
            + "  right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),10),4) as nam,"
            + "  c.Gioitinh,"
            + "  c.Diachi, c.DiachiHK, c.Mapb,b.madvi"
            + "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            + "  (SELECT Sobhxh, b.Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL b WHERE left(b.Madvi,2) = 'BE' "
            + "  AND LEFT(Maqt,6)=denthang AND LEFT(Maqt,6)<='" + txtMaqt.Text + "'  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + "  GROUP BY Sobhxh, Loaidt, b.Ma_Nte) a  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            + "  AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang and left(b.Madvi,2) = 'BE'"
            + "  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt <> 0 AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 and left(b.sobhxh,1) = 'Y'"
            + "  ) b on a.hoten = b.hoten  and a.ngay = b.ngay and a.thang = b.thang and a.nam = b.nam"
            + "  and a.gioitinh = b.gioitinh and a.mapb = b.mapb"
            + "  order by a.stt ";
            return sql;
        }
        private string kiemtratrungtheTE(string ho,string ten,string ngaysinh,string gioitinh,string maqt)
        {
            string sql = " SELECT TOP 3 a.Madvi, a.Sobhxh, a.Sokcb, a.Ho, a.Ten, a.Ngaysinh, "
            + "  a.Gioitinh, a.Socmnd, a.Diachi, a.Mapb FROM HOSOCN a  "
            + "  WHERE a.Ho='" + ho + "'  AND a.Ten='" + ten + "'  AND a.Ngaysinh='" + ngaysinh + "'   AND a.Gioitinh = " + gioitinh 
            + "  AND EXISTS  (SELECT g.Sobhxh FROM HSC47_DTL g,  "
            + "  (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt FROM HSC47_DTL a, DMDONVI b "
            + "  WHERE a.Madvi=b.Madvi AND b.MakhoiKCB='TE'   AND LEFT(a.Maqt,6)=a.Denthang AND "
            + "  LEFT(a.Maqt,6)<='" + maqt + "'  AND CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + "  GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) f  WHERE a.Sobhxh=g.Sobhxh AND g.Sobhxh=f.Sobhxh "
            + "  AND g.Loaidt=f.Loaidt AND g.Ma_Nte=f.Ma_Nte AND g.Maqt=f.Maqt AND LEFT(g.Maqt,6)=g.Denthang "
            + "  AND g.Madvi=f.Madvi  AND g.Hsl+g.Pc1+g.Pc2+g.Pc3+g.Pc4+g.Pc5+g.Ml+g.Mlyt<>0 AND "
            + "  CHARINDEX(g.Pa,'TT/TU/TN/GN/DN/CD')=0)";
            return sql;
        }
        private string kiemtratrungthedonvi()
        {
            string sql = " select a.stt, b.sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk,a.mapb from ("
            + "  SELECT stt,hoten,ngaysinh,"
            + "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
            + "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
            + "  right(left(ngaysinh,10),4) as nam,"
            + "  gioitinh,diachi,mapb"
            + "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0,7) + "'"
            + "  ) a left join "
            + "  (SELECT b.Sobhxh,rtrim(c.Ho) + ' ' + rtrim(c.Ten) as hoten, c.Sokcb, "
            + "  case when c.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), c.ngaysinh, 103),2) end as ngay,"
            + "  case when c.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),5),2) end as thang,"
            + "  right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),10),4) as nam,"
            + "  c.Gioitinh,"
            + "  c.Diachi, c.DiachiHK, c.Mapb,b.madvi"
            + "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
            + "  (SELECT Sobhxh, b.Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL b WHERE b.Madvi = '" + cboMadvi.Text.Substring(0,7) + "'" 
            + "  AND LEFT(Maqt,6)=denthang AND LEFT(Maqt,6)<='" + txtMaqt.Text + "'  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + "  GROUP BY Sobhxh, Loaidt, b.Ma_Nte) a  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
            + "  AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang and b.Madvi = '" + cboMadvi.Text.Substring(0,7) + "'"
            + "  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt <> 0 AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 and left(b.sobhxh,1) = 'Y'"
            + "  ) b on UPPER(a.hoten) = UPPER(b.hoten) and a.ngay = b.ngay and a.thang = b.thang and a.nam = b.nam"
            + "  and a.gioitinh = b.gioitinh "
            + "  order by a.stt ";
            return sql;
        }
        private string kiemtratrungthedonvi(string ho, string ten, string ngaysinh, string gioitinh, string maqt)
        {
            string sql = " SELECT TOP 3 a.Madvi, a.Sobhxh, a.Sokcb, a.Ho, a.Ten, a.Ngaysinh, "
            + "  a.Gioitinh, a.Socmnd, a.Diachi, a.Mapb FROM HOSOCN a  "
            + "  WHERE a.Ho='" + ho + "'  AND a.Ten='" + ten + "'  AND a.Ngaysinh='" + ngaysinh + "'   AND a.Gioitinh = " + gioitinh
            + "  AND EXISTS  (SELECT g.Sobhxh FROM HSC47_DTL g,  "
            + "  (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt FROM HSC47_DTL a, DMDONVI b "
            + "  WHERE a.Madvi=b.Madvi and b.Madvi = '" + cboMadvi.Text.Substring(0,7) + "' AND LEFT(a.Maqt,6)=a.Denthang AND "
            + "  LEFT(a.Maqt,6)<='" + maqt + "'  AND CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 "
            + "  GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) f  WHERE a.Sobhxh=g.Sobhxh AND g.Sobhxh=f.Sobhxh "
            + "  AND g.Loaidt=f.Loaidt AND g.Ma_Nte=f.Ma_Nte AND g.Maqt=f.Maqt AND LEFT(g.Maqt,6)=g.Denthang "
            + "  AND g.Madvi=f.Madvi  AND g.Hsl+g.Pc1+g.Pc2+g.Pc3+g.Pc4+g.Pc5+g.Ml+g.Mlyt<>0 AND "
            + "  CHARINDEX(g.Pa,'TT/TU/TN/GN/DN/CD')=0)";
            return sql;
        }
        private string kiemtratrungHSTE()
        {
            string sql = " select a.stt, b.sobhxh,b.sokcb,a.hoten,a.ngaysinh,a.gioitinh,a.diachi,b.diachi,b.diachihk,a.mapb from ("
           + "  SELECT stt,hoten,ngaysinh,"
           + "  case when namsinh = 'X' then '01' else left(ngaysinh,2) end as ngay,"
           + "  case when namsinh = 'X' then '01' else right(left(ngaysinh,5),2) end as thang,"
           + "  case when namsinh = 'X' then ngaysinh else right(left(ngaysinh,10),4) end as nam,"
           + "  gioitinh,diachi,MAPB"
           + "  FROM KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0, 7) + "'"
           + "  ) a left join "
           + "  (SELECT b.Sobhxh,rtrim(c.Ho) + ' ' + rtrim(c.Ten) as hoten, c.Sokcb, "
           + "  case when c.namsinh = 1 then '01' else left(CONVERT(VARCHAR(10), c.ngaysinh, 103),2) end as ngay,"
           + "  case when c.namsinh = 1 then '01' else right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),5),2) end as thang,"
           + "  right(left(CONVERT(VARCHAR(10), c.ngaysinh, 103),10),4) as nam,"
           + "  c.Gioitinh,"
           + "  c.Diachi, c.DiachiHK, c.Mapb,b.madvi"
           + "  FROM HSC47_DTL b LEFT JOIN HOSOCN c ON b.Sobhxh=c.Sobhxh,  "
           + "  (SELECT Sobhxh, b.Ma_Nte, Loaidt, MAX(Maqt) AS Maqt FROM HSC47_DTL b WHERE left(b.Madvi,2) = 'BE' "
           + "  AND LEFT(Maqt,6)=denthang AND LEFT(Maqt,6)<='" + txtMaqt.Text + "'  AND CHARINDEX(Pa,'TT/TU/TN/GN/DN/CD')=0 "
           + "  GROUP BY Sobhxh, Loaidt, b.Ma_Nte) a  WHERE a.Sobhxh=b.Sobhxh AND a.Loaidt=b.Loaidt "
           + "  AND a.Ma_Nte=b.Ma_Nte AND a.Maqt=b.Maqt AND LEFT(b.Maqt,6)=b.Denthang and left(b.Madvi,2) = 'BE'"
           + "  AND b.Hsl+b.Pc1+b.Pc2+b.Pc3+b.Pc4+b.Pc5+b.Ml+b.Mlyt <> 0 AND CHARINDEX(b.Pa,'TT/TU/TN/GN/DN/CD')=0 and left(b.sobhxh,1) = 'Y'"
           + "  ) b on a.hoten = b.hoten  and a.ngay = b.ngay and a.thang = b.thang and a.nam = b.nam"
           + "  and a.gioitinh = b.gioitinh "
           + "  order by a.stt ";
            return sql;            
        }
        
        private string kiemtratrungthetheoxa()
        {
            string sql = " SELECT c.Madvi, a.Sobhxh, a.Ho, a.Ten, a.Sokcb,"
            + " a.Ngaysinh, a.Gioitinh, a.Diachi,a.DiachiHK, a.Mapb, cast(0 as bit) as checkbox "
            + " FROM HOSOCN a, "
            + " (SELECT Ho, Ten, Ngaysinh, Gioitinh,   COUNT(*) As Dem "  
            + " FROM HOSOCN WHERE Sobhxh IN ( "
            + " SELECT d.Sobhxh FROM HSC47_DTL d, " 
            + " (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt "
            + " FROM HSC47_DTL a, DMDONVI b WHERE a.Madvi=b.Madvi  AND left(a.madvi,1) IN ('A','B') and "
            + " (b.MA_XA = '" + cboMaxa.Text.Substring(0, 2) + "' or b.MA_XA = '99')  AND LEFT(a.Maqt,6)=a.Denthang  AND LEFT(a.Maqt,6)<='" + txtMaqt.Text + "' AND "
            + " CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) e  "
            + " WHERE d.Madvi=e.Madvi AND d.Sobhxh=e.Sobhxh AND d.Loaidt=e.Loaidt AND d.Ma_Nte=e.Ma_Nte AND d.Maqt=e.Maqt "
            + " AND LEFT(d.Maqt,6)=d.Denthang  AND CHARINDEX(d.Pa,'TT/TU/TN/GN/DN/CD')=0 AND "
            + " (d.Hsl+d.Pc1+d.Pc2+d.Pc3+d.Pc4+d.Pc5+d.Ml+d.Mlyt<>0 OR d.Pa='TS'))"
            + " GROUP BY Ho, Ten, Ngaysinh , Gioitinh   HAVING COUNT(*) > 1) b,"
            + " (SELECT d.Madvi, d.Sobhxh, d.Hsl, d.Pc1, d.Pc2, d.Pc3, d.Pc4, d.Pc5, d.Ml, d.Mlyt FROM HSC47_DTL d,"
            + " (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt FROM HSC47_DTL a, DMDONVI b "
            + " WHERE a.Madvi=b.Madvi and left(a.madvi,1) IN ('A','B') AND (b.MA_XA = '" + cboMaxa.Text.Substring(0, 2) + "' or b.MA_XA = '99') AND LEFT(a.Maqt,6)=a.Denthang  AND "
            + " LEFT(a.Maqt,6)<='" + txtMaqt.Text + "' AND CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) e "
            + " WHERE d.Madvi=e.Madvi AND d.Sobhxh=e.Sobhxh AND d.Loaidt=e.Loaidt AND d.Ma_Nte=e.Ma_Nte AND d.Maqt=e.Maqt AND "
            + " LEFT(d.Maqt,6)=d.Denthang  AND CHARINDEX(d.Pa,'TT/TU/TN/GN/DN/CD')=0 AND "
            + " (d.Hsl+d.Pc1+d.Pc2+d.Pc3+d.Pc4+d.Pc5+d.Ml+d.Mlyt<>0 OR d.Pa='TS') ) c "
            + " WHERE a.Sobhxh=c.Sobhxh  AND a.Ho=b.Ho AND a.Ten=b.Ten AND a.Ngaysinh=b.Ngaysinh "
            + " AND a.Gioitinh=b.Gioitinh and a.sobhxh not in (select sobhxh from KT_TRUNGTHE.dbo.locthe)"
            + " order by a.HO,a.TEN,a.NGAYSINH,a.GIOITINH";
            return sql;
        }
        private string kiemtratrungtheTEME()
        {
            string sql = " SELECT c.Madvi, a.Sobhxh, a.Ho, a.Ten, a.Sokcb,"
            + " a.Ngaysinh, a.Gioitinh, a.Diachi,a.DiachiHK, a.Mapb, cast(0 as bit) as checkbox "
            + " FROM HOSOCN a, "
            + " (SELECT Ho, Ten, Ngaysinh, Gioitinh, mapb, COUNT(*) As Dem "
            + " FROM HOSOCN WHERE Sobhxh IN ( "
            + " SELECT d.Sobhxh FROM HSC47_DTL d, "
            + " (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt "
            + " FROM HSC47_DTL a, DMDONVI b WHERE a.Madvi=b.Madvi  AND left(a.madvi,2) = 'BE' and "
            + " LEFT(a.Maqt,6)=a.Denthang  AND LEFT(a.Maqt,6)<='" + txtMaqt.Text + "' AND "
            + " CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) e  "
            + " WHERE d.Madvi=e.Madvi AND d.Sobhxh=e.Sobhxh AND d.Loaidt=e.Loaidt AND d.Ma_Nte=e.Ma_Nte AND d.Maqt=e.Maqt "
            + " AND LEFT(d.Maqt,6)=d.Denthang  AND CHARINDEX(d.Pa,'TT/TU/TN/GN/DN/CD')=0 AND "
            + " (d.Hsl+d.Pc1+d.Pc2+d.Pc3+d.Pc4+d.Pc5+d.Ml+d.Mlyt<>0 OR d.Pa='TS'))"
            + " GROUP BY Ho, Ten, Ngaysinh , Gioitinh,mapb HAVING COUNT(*) > 1) b,"
            + " (SELECT d.Madvi, d.Sobhxh, d.Hsl, d.Pc1, d.Pc2, d.Pc3, d.Pc4, d.Pc5, d.Ml, d.Mlyt FROM HSC47_DTL d,"
            + " (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt FROM HSC47_DTL a, DMDONVI b "
            + " WHERE a.Madvi=b.Madvi and left(a.madvi,2) = 'BE' AND LEFT(a.Maqt,6)=a.Denthang  AND "
            + " LEFT(a.Maqt,6)<='" + txtMaqt.Text + "' AND CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) e "
            + " WHERE d.Madvi=e.Madvi AND d.Sobhxh=e.Sobhxh AND d.Loaidt=e.Loaidt AND d.Ma_Nte=e.Ma_Nte AND d.Maqt=e.Maqt AND "
            + " LEFT(d.Maqt,6)=d.Denthang  AND CHARINDEX(d.Pa,'TT/TU/TN/GN/DN/CD')=0 AND "
            + " (d.Hsl+d.Pc1+d.Pc2+d.Pc3+d.Pc4+d.Pc5+d.Ml+d.Mlyt<>0 OR d.Pa='TS') ) c "
            + " WHERE a.Sobhxh=c.Sobhxh  AND a.Ho=b.Ho AND a.Ten=b.Ten AND year(a.Ngaysinh)=year(b.Ngaysinh) "
            + " AND a.mapb=b.mapb and a.sobhxh not in (select sobhxh from KT_TRUNGTHE.dbo.locthe)"
            + " order by a.HO,a.TEN,a.NGAYSINH,a.GIOITINH";
            return sql;
        }
        private string kiemtratrungthetatca()
        {
            string sql = " SELECT c.Madvi, a.Sobhxh, a.Ho, a.Ten, a.Sokcb,"
            + " a.Ngaysinh, a.Gioitinh, a.Diachi,a.DiachiHK, a.Mapb, cast(0 as bit) as checkbox "
            + " FROM HOSOCN a, "
            + " (SELECT Ho, Ten, Ngaysinh, Gioitinh,   COUNT(*) As Dem "
            + " FROM HOSOCN WHERE Sobhxh IN ( "
            + " SELECT d.Sobhxh FROM HSC47_DTL d, "
            + " (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt "
            + " FROM HSC47_DTL a, DMDONVI b WHERE a.Madvi=b.Madvi  AND left(a.madvi,1) IN ('A','B')  and  left(a.madvi,2) <> 'BI' and "
            + " LEFT(a.Maqt,6)=a.Denthang  AND LEFT(a.Maqt,6)<='" + txtMaqt.Text + "' AND "
            + " CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) e  "
            + " WHERE d.Madvi=e.Madvi AND d.Sobhxh=e.Sobhxh AND d.Loaidt=e.Loaidt AND d.Ma_Nte=e.Ma_Nte AND d.Maqt=e.Maqt "
            + " AND LEFT(d.Maqt,6)=d.Denthang  AND CHARINDEX(d.Pa,'TT/TU/TN/GN/DN/CD')=0 AND "
            + " (d.Hsl+d.Pc1+d.Pc2+d.Pc3+d.Pc4+d.Pc5+d.Ml+d.Mlyt<>0 OR d.Pa='TS'))"
            + " GROUP BY Ho, Ten, Ngaysinh , Gioitinh   HAVING COUNT(*) > 1) b,"
            + " (SELECT d.Madvi, d.Sobhxh, d.Hsl, d.Pc1, d.Pc2, d.Pc3, d.Pc4, d.Pc5, d.Ml, d.Mlyt FROM HSC47_DTL d,"
            + " (SELECT a.Madvi, a.Sobhxh, a.Ma_Nte, a.Loaidt, MAX(a.Maqt) AS Maqt FROM HSC47_DTL a, DMDONVI b "
            + " WHERE a.Madvi=b.Madvi and left(a.madvi,1) IN ('A','B') and  left(a.madvi,2) <> 'BI' AND LEFT(a.Maqt,6)=a.Denthang  AND "
            + " LEFT(a.Maqt,6)<='" + txtMaqt.Text + "' AND CHARINDEX(a.Pa,'TT/TU/TN/GN/DN/CD')=0 GROUP BY a.Madvi, a.Sobhxh, a.Loaidt, a.Ma_Nte) e "
            + " WHERE d.Madvi=e.Madvi AND d.Sobhxh=e.Sobhxh AND d.Loaidt=e.Loaidt AND d.Ma_Nte=e.Ma_Nte AND d.Maqt=e.Maqt AND "
            + " LEFT(d.Maqt,6)=d.Denthang  AND CHARINDEX(d.Pa,'TT/TU/TN/GN/DN/CD')=0 AND "
            + " (d.Hsl+d.Pc1+d.Pc2+d.Pc3+d.Pc4+d.Pc5+d.Ml+d.Mlyt<>0 OR d.Pa='TS') ) c "
            + " WHERE a.Sobhxh=c.Sobhxh  AND a.Ho=b.Ho AND a.Ten=b.Ten AND a.Ngaysinh=b.Ngaysinh "
            + " AND a.Gioitinh=b.Gioitinh and a.sobhxh not in (select sobhxh from KT_TRUNGTHE.dbo.locthe)"
            + " order by a.HO,a.TEN,a.NGAYSINH,a.GIOITINH";
            return sql;
        }
        private void Kiemtrathe(ModuleBHXH.loai loai)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringlocal"].ConnectionString;
            SqlDataReader  dr;
            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    con.Open();
                    // xoa ban
                    
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        int i=1;
                        cmd.CommandType = CommandType.Text;
                        while (fa.Rows.Count >i && fa.Cols["hoten"][i].ToString() != "" )
                        {
                            if (loai == ModuleBHXH.loai.TRUNGTHE)
                            {
                                cmd.CommandText = kiemtratrungthe(getho(fa.Cols["hoten"][i].ToString()), getten(fa.Cols["hoten"][i].ToString()),
                                    FormatNgaySinh(fa.Cols["ngaysinh"][i].ToString()), fa.Cols["gioitinh"][i].ToString(), txtMaqt.Text);
                            }
                            else if (loai == ModuleBHXH.loai.TRUNGTHETE || loai == ModuleBHXH.loai.TRUNGHSTE)
                            {
                                cmd.CommandText = kiemtratrungtheTE(getho(fa.Cols["hoten"][i].ToString()), getten(fa.Cols["hoten"][i].ToString()),
                                    FormatNgaySinh(fa.Cols["ngaysinh"][i].ToString()), fa.Cols["gioitinh"][i].ToString(), txtMaqt.Text);
                            }
                            else if (loai == ModuleBHXH.loai.TRUNGTHEDVI)
                            {
                                cmd.CommandText = kiemtratrungthedonvi(getho(fa.Cols["hoten"][i].ToString()), getten(fa.Cols["hoten"][i].ToString()),
                                    FormatNgaySinh(fa.Cols["ngaysinh"][i].ToString()), fa.Cols["gioitinh"][i].ToString(), txtMaqt.Text);
                            }
                            dr = cmd.ExecuteReader();
                            if (dr.HasRows)
                            {
                                dr.Read();
                                fa.Cols["sobhxh"][i] = dr["sobhxh"].ToString();
                                fa.Cols["sokcb"][i] = dr["sokcb"].ToString();
                            }
                            i++;
                            dr.Close();
                        }

                    }
                    con.Close();

                }   

            }
            catch (SqlException e)
            {
                MessageBox.Show("Kiểm tra lại dử liệu");
                // the transaction scope will take care of rolling back
            }
        }
        public string getten(string str)
        {
            str = str.Trim();
            string ten = str.Substring(str.LastIndexOf(' ') + 1);
            return ten.Trim();
            
        }
        public string getho(string str)
        {
            str = str.Trim();
            String ho = str.Substring(0, str.IndexOf(' ')).Trim();
            string td = " ";
            int lg = str.LastIndexOf(' ') - ho.Length - 1;
            if (lg > 0)
            {
                td = td + str.Substring(str.IndexOf(' ') + 1, str.LastIndexOf(' ') - ho.Length - 1).Trim();
            }
            return (ho + td).Trim();

        }
        public string FormatNgaySinh(string ngaysinh)
        {
            string ngay = ngaysinh.Substring(0,ngaysinh.IndexOf('/'));
            string thang = ngaysinh.Substring(ngaysinh.IndexOf('/') + 1,ngaysinh.LastIndexOf('/') - ngay.Length-1);
            string nam = ngaysinh.Substring(ngaysinh.LastIndexOf('/') + 1);
            return thang + "/" + ngay + "/" + nam;
        }

        private void Importfile(int loai)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringlocal"].ConnectionString;
            string FileName = txtDuongdan.Text;
            string sql="";
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + FileName + " ;Extended Properties=Excel 8.0";
            if (loai == (int)ModuleBHXH.loai.LAYSOSOTAM)
            {
                if (cboLoaiHSCN.SelectedIndex == 2)
                {
                    sql = hosocn(); 
                }
                else
                {
                    sql = hosothamgia();
                }
            }
            else if (loai == (int)ModuleBHXH.loai.LAYSOSOTAMMAX)
            {
                if (cboLoaiHSCN.SelectedIndex == 2)
                {
                    sql = hosocnmax();
                }
                else
                {
                    sql = hosothamgiamax();
                }
            }
            else if (loai == (int)ModuleBHXH.loai.TRUNGTHE)
            {
                sql = kiemtratrungthe();
            }
            else if (loai == (int)ModuleBHXH.loai.TRUNGTHETE)
            {
                sql = kiemtratrungtheTE();
            }
            else if (loai == (int)ModuleBHXH.loai.TRUNGTHEDVI)
            {
                sql = kiemtratrungthedonvi();
            }
            else if (loai == (int)ModuleBHXH.loai.TRUNGHSTE)
            {
                sql = kiemtratrungHSTE();
            }
            bool flag = false;
            
            try
            {
                    using (SqlConnection con = new SqlConnection(sqlConnectionString))
                    {
                        con.Open();
                        // xoa ban
                        using (SqlCommand cmd = con.CreateCommand())
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = "delete KT_TRUNGTHE.dbo.BHYTSOTAM WHERE MADVI = '" + cboMadvi.Text.Substring(0,7) + "'";
                            flag = cmd.ExecuteNonQuery() == 1;
                        }
                        con.Close();
                        using (SqlBulkCopy bulkCopy =
                          new SqlBulkCopy(sqlConnectionString))
                        {
                            bulkCopy.DestinationTableName = "KT_TRUNGTHE.dbo.BHYTSOTAM";
                            bulkCopy.WriteToServer(tb);
                           
                        }

                        con.Open();

                        using (SqlCommand cmd = con.CreateCommand())
                        {
                            SqlDataAdapter myAdapter = new SqlDataAdapter();
                            DataSet ds = new DataSet();
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandTimeout = 150;
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                            myAdapter.SelectCommand = cmd;
                            myAdapter.Fill(ds);
                            fa.DataSource = ds.Tables[0];
                           
                        }

                    }


                    
                
            }
            catch (SqlException e)
            {
                MessageBox.Show("Kiểm tra lại dử liệu");
                // the transaction scope will take care of rolling back
            }
        }
        private void btnSosomax_Click(object sender, EventArgs e)
        {
            if (CheckCondtion() == false) { return; }
            Cursor.Current = Cursors.WaitCursor;
            Importfile(1);
            FormatGrid();
            canhbaotrungmax();
            Cursor.Current = Cursors.Default;
        }
        private void btnsochay_Click(object sender, EventArgs e)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringlocal"].ConnectionString;
            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    con.Open();
                    using (SqlCommand cmd = con.CreateCommand())
                    {

                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "DBCC CHECKIDENT('KT_TRUNGTHE.dbo.BHYTSOTAM', RESEED, 0)";
                        cmd.ExecuteNonQuery();

                    }
                    con.Close();
                    MessageBox.Show("Tạo số chạy thành công");
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
                // the transaction scope will take care of rolling back
            }
        }
        private void btnXoa_Click(object sender, EventArgs e)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringlocal"].ConnectionString;
            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    con.Open();
                    using (SqlCommand cmd = con.CreateCommand())
                    {

                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "delete KT_TRUNGTHE.dbo.BHYTSOTAM";
                        cmd.ExecuteNonQuery();

                    }
                    con.Close();
                    MessageBox.Show("Xóa thành công");
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
                // the transaction scope will take care of rolling back
            }
        }
        private void btnTrungthe_Click(object sender, EventArgs e)
        {
            //if (CheckCondtion() == false) { return; }
            Cursor.Current = Cursors.WaitCursor;
            //Importfile((int)ModuleBHXH.loai.TRUNGTHE);
            Kiemtrathe(ModuleBHXH.loai.TRUNGTHE);
            FormatGrid();
            canhbaotrung();
            Cursor.Current = Cursors.Default;
        }
        private void btnTrungtheTE_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            //Importfile((int)ModuleBHXH.loai.TRUNGTHETE);
            Kiemtrathe(ModuleBHXH.loai.TRUNGTHETE);
            FormatGrid();
            canhbaotrung();
            Cursor.Current = Cursors.Default;
        }
        private void btnTTdvi_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            //Importfile((int)ModuleBHXH.loai.TRUNGTHEDVI);
            Kiemtrathe(ModuleBHXH.loai.TRUNGTHEDVI);
            FormatGrid();
            canhbaotrung();
            Cursor.Current = Cursors.Default;
        }
        private void btnHSTE_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            //Importfile((int)ModuleBHXH.loai.TRUNGHSTE);
            Kiemtrathe(ModuleBHXH.loai.TRUNGHSTE);
            FormatGrid();
            canhbaotrung();
            Cursor.Current = Cursors.Default;
        }
        private void LoadDataXa()
        {
            // Load the customer object from the existing XML file (if any)...
            if (File.Exists(XML_FILE_XA) == true)
            {
                // Load the customer object from the XML file using our custom class...
                Maxa maxa = ObjectXMLSerializer<Maxa>.Load(XML_FILE_XA);

                if (maxa == null)
                {
                    MessageBox.Show("Unable to load customer object from file '" + XML_FILE_XA + "'!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else  // Load customer properties into the form...
                {
                    this.LoadXatoForm(maxa);
                }
            }
            
        }
        private void LoadXatoForm(Maxa maxa)
        {

            cboMaxa.ClearItems();
            cboMaxa.DefColWidth = 100;

            maxa.Names.Sort();
            foreach (string Item in maxa.Names)
            {
                cboMaxa.AddItem(Item);
            }
            cboMaxa.AllowSort = true;
            cboMaxa.Columns[0].Caption = "Name";
            cboMaxa.ColumnWidth = 250;


        }
        private void LoadDataKhoi()
        {
            // Load the customer object from the existing XML file (if any)...
            if (File.Exists(XML_FILE_KHOI) == true)
            {
                // Load the customer object from the XML file using our custom class...
                Makhoi makhoi = ObjectXMLSerializer<Makhoi>.Load(XML_FILE_KHOI);

                if (makhoi == null)
                {
                    MessageBox.Show("Unable to load customer object from file '" + XML_FILE_KHOI + "'!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else  // Load customer properties into the form...
                {
                    this.LoadKhoitoForm(makhoi);
                }
            }

        }
        private void LoadKhoitoForm(Makhoi makhoi)
        {
             
            cboMakhoi.ClearItems();
            cboMakhoi.DefColWidth = 100;

            makhoi.Names.Sort();
            foreach (string Item in makhoi.Names)
            {
                cboMakhoi.AddItem(Item);
            }
            cboMakhoi.AllowSort = true;
            cboMakhoi.Columns[0].Caption = "Name";
            cboMakhoi.ColumnWidth = 250;


        }
        private void LoadMadvitoForm()
        {
            DataSet ds = new DataSet();
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringlocal"].ConnectionString;
            string sql = "select MADVI + ' - ' + RTRIM(TENDVI) + ' - ' + RTRIM(MA_XA) AS NAM from DMDONVI WHERE LEFT(MADVI,1) IN ('A','B')";
            using (SqlConnection con = new SqlConnection(sqlConnectionString))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    SqlDataAdapter myAdapter = new SqlDataAdapter();
                   
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    myAdapter.SelectCommand = cmd;
                    myAdapter.Fill(ds);
                   
                }
            }
            cboMadvi.DataSource = ds.Tables[0];
            cboMadvi.DefColWidth = 300;
            cboMadvi.ColumnWidth = 300;


        }
        private void btnXa_Click(object sender, EventArgs e)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringlocal"].ConnectionString;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    con.Open();
                    
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        SqlDataAdapter myAdapter = new SqlDataAdapter();
                        DataSet ds = new DataSet();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 500;
                        cmd.CommandText = kiemtratrungthetheoxa();
                        cmd.ExecuteNonQuery();
                        myAdapter.SelectCommand = cmd;
                        myAdapter.Fill(ds);
                        fa.DataSource = ds.Tables[0];
                        FormatGrid1();
                    }

                }

            }
            catch (SqlException ex)
            {
                MessageBox.Show("Kiểm tra lại dử liệu");
                // the transaction scope will take care of rolling back
            }
            Cursor.Current = Cursors.Default;
        }
        private void btntatca_Click(object sender, EventArgs e)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringlocal"].ConnectionString;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    con.Open();

                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        SqlDataAdapter myAdapter = new SqlDataAdapter();
                        DataSet ds = new DataSet();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 1000;
                        cmd.CommandText = kiemtratrungthetatca();
                        cmd.ExecuteNonQuery();
                        myAdapter.SelectCommand = cmd;
                        myAdapter.Fill(ds);
                        fa.DataSource = ds.Tables[0];
                        FormatGrid1();
                    }

                }

            }
            catch (SqlException ex)
            {
                MessageBox.Show("Kiểm tra lại dử liệu");
                // the transaction scope will take care of rolling back
            }
            Cursor.Current = Cursors.Default;
        }
        private void btntreem_Click(object sender, EventArgs e)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringlocal"].ConnectionString;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    con.Open();

                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        SqlDataAdapter myAdapter = new SqlDataAdapter();
                        DataSet ds = new DataSet();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 1000;
                        cmd.CommandText = kiemtratrungtheTEME();
                        cmd.ExecuteNonQuery();
                        myAdapter.SelectCommand = cmd;
                        myAdapter.Fill(ds);
                        fa.DataSource = ds.Tables[0];
                        FormatGrid1();
                    }

                }

            }
            catch (SqlException ex)
            {
                MessageBox.Show("Kiểm tra lại dử liệu");
                // the transaction scope will take care of rolling back
            }
            Cursor.Current = Cursors.Default;
        }
        private void btnKotrung_Click(object sender, EventArgs e)
        {
            string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionStringLocal"].ConnectionString;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    con.Open();

                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        
                        cmd.CommandType = CommandType.Text;
                        for (int i = 1;i<fa.Rows.Count;i++)
                        {
                            if ((bool)fa.Rows[i]["checkbox"] == true)
                            {
                                cmd.CommandText = "insert into KT_TRUNGTHE.dbo.LOCTHE values ('" + fa.Rows[i]["sobhxh"] + "','" + fa.Rows[i]["MADVI"] + "')";
                                cmd.ExecuteNonQuery();
                            }
                        }
                     
                    }

                }

            }
            catch (SqlException ex)
            {
                MessageBox.Show("Kiểm tra lại dử liệu");
                // the transaction scope will take care of rolling back
            }
            
            Cursor.Current = Cursors.Default;
            btnXa_Click( null, null);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            // choose file
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.DefaultExt = "xls";
            dlg.FileName = "*.xls";
            if (dlg.ShowDialog() != DialogResult.OK)
                return;
            // save grid as sheet in the book
            FileFlags flags = FileFlags.None;
            fa.SaveGrid(dlg.FileName, FileFormatEnum.Excel, flags);
        }

        

        

      

       
        
    }
}
